package org.NooLab.texx.storage.db.h2;

import java.sql.Array;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.RowId;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Vector;

import org.NooLab.randomgraph.storage.db.iciql.AliasIntf;
import org.h2.engine.Session;
import org.h2.index.Index;
import org.h2.index.IndexType;
import org.h2.result.Row;
import org.h2.table.IndexColumn;
import org.h2.table.Table;


//LOC 440


public class DocoDbTable {

	private Connection connection;
	String name ;
	private DBColumn dbColumn;
	
	private ArrayList<DBColumn> columns = new ArrayList<DBColumn>() ;
	private ArrayList<String> columnNames = new ArrayList<String>() ;
	
	AliasIntf alias;
	
	// --------------------------------------------------------------------------------------
	
	public DocoDbTable(Connection conn, String tablename){
		connection = conn; 
		name = tablename ;
		
		getListofColumns();
	}
	
	
	public int countColumns(){
		return columns.size();
	}
	
	public int countRecords(){
		String sql;
		ResultSet resultset;
		int rVal=-1;
		
		sql = "SELECT COUNT(*) FROM "+ name+" ;"; 
		
		try{
			PreparedStatement prep = connection.prepareStatement( sql );
			resultset = prep.executeQuery();
			 
			 
			while (resultset.next()){
				rVal = resultset.getInt(1);
			}
			
			resultset.close();

		} catch( SQLException sx ){
			sx.printStackTrace();
		} 		
		
		return rVal;
	}
	
	public int getlastRecordIndex(String idcolumn){
		int rVal=-1;
		String sql;
		boolean rb=false;
		ResultSet resultset;
		int r=-1,z=0;
		
		sql = "SELECT * from "+name+" A WHERE A."+idcolumn+" = (SELECT MAX("+idcolumn+") FROM "+name+");" ;
		
		try{
			if (connection.isClosed()){
				return -3;
			}
			
			PreparedStatement prep = connection.prepareStatement( sql );
			resultset = prep.executeQuery();
			
			while (resultset.next()){
				r = resultset.getInt(idcolumn);
				z++;
			}
			if (z>0){
				 rVal=r;
			}
		}catch(SQLException sx){
			sx.printStackTrace();
		}
		
		return rVal;
	}
	
	
	public DBColumn getColumnbyName(String field){
		DBColumn col=null ;
		
		for (int i=0;i<columns.size();i++){
			if (field.contentEquals(columns.get(i).labelname)){
				col = columns.get(i);
				break;
			}
		}
		return col;
	}
	
	public String[] getRecordbyRow( int rowposition){
		
		return getRecordbyRow(rowposition, new String[0]);
	}
	public String[] getRecordbyRow( int rowposition, String  ...fields){
		
		return null;
	}
	
	
	public String[] executeSQL( String sql ){
		return executeSQL( sql, new String[0]);
	}
	
	public String[] executeSQL( String sql, String  ...fields){
		return null;
	}
	
	
	public String[] getAllRecords(){
		
		return getAllRecords( new String[0]);
	}
	
	public String[] getAllRecords( String  ...fields){
		 
		
		String[] rStr = null;
		String cellvalue, rowstring="";
		String sql,fieldlist="";
		Statement stmt;
		ResultSet resultset;		
		Vector<Integer> activeColumns = new Vector<Integer>();
		int activeColumnsCount=0,z,zz;
		Object obj;
		DBColumn col ;
		
		if (fields==null){
			fields = new String[0];
		}
		
		if (fields.length==0){
			fieldlist = "*" ;
			activeColumnsCount = columns.size();
			fields = new String[columns.size()] ;
			for (int i=0;i<columns.size();i++){
				fields[i] = columns.get(i).labelname; // hold that globally in table object !
			}
		}
		else{
			for (int i=0;i<fields.length;i++){
				if ((fields[i].length()>0) && ( columnNames.contains(fields[i]))){
					fieldlist = fieldlist + fields[i];
					
					if (i<fields.length-1){
						fieldlist = fieldlist + ",";
					}
					activeColumnsCount++;
				}
				
			}
		}
		
		
		sql = "SELECT "+ fieldlist +" FROM "+ name+"  ;" ; 
		
		try{
			
			stmt =  connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
			// PreparedStatement prep = connection.prepareStatement( sql );
			resultset = stmt.executeQuery(sql);
			 
			// resultset = stmt.execute(sql, Statement.RETURN_GENERATED_KEYS);
			
			z=0; 
			while (resultset.next()){
				z++;
			}
			
			rStr = new String[z];
			resultset.beforeFirst();
			zz=0;
			
			while (resultset.next()){
				rowstring ="";
				
				for (int c=1; c<=activeColumnsCount;c++){
					
					obj = resultset.getObject(c);
					
					col = getColumnbyName( fields[c-1]);
					
					cellvalue =  col.getStringfromCellValue( obj );
					
					rowstring = rowstring + cellvalue;
					if (c<activeColumnsCount){
						rowstring = rowstring + " \t" ;
					}
					 
				} // assembling the row
				
				rStr[zz] = rowstring;
				zz++;
			} // resultset ->  getStringfromCellValue( c, obj );
			 
			
			resultset.close();

			connection.commit();
			
		} catch( SQLException sx ){
			String msg = sx.getMessage();
			
			sx.printStackTrace();
		} 		
				
		
		return rStr;		
		
	}
	
	public String[] getRecordbyIndex(int index){
		
		return getRecordbyIndex(index, new String[0]);
	}
	
	public String[] getRecordbyIndex(int index, String  ...fields){
		 
	
		String[] rStr = null;
		String cellvalue, rowstring="";
		String sql,fieldlist="";
		Statement stmt;
		ResultSet resultset;		
		Vector<Integer> activeColumns = new Vector<Integer>();
		int activeColumnsCount=0,z;
		Object obj;
		DBColumn col ;
		
		if (fields==null){
			fields = new String[0];
		}
		
		if (fields.length==0){
			fieldlist = "*" ;
			activeColumnsCount = columns.size();
			fields = new String[columns.size()] ;
			for (int i=0;i<columns.size();i++){
				fields[i] = columns.get(i).labelname; // hold that globally in table object !
			}
		}
		else{
			for (int i=0;i<fields.length;i++){
				if ((fields[i].length()>0) && ( columnNames.contains(fields[i]))){
					fieldlist = fieldlist + fields[i];
					
					if (i<fields.length-1){
						fieldlist = fieldlist + ",";
					}
					activeColumnsCount++;
				}
				
			}
		}
		
		
		
		sql = "SELECT "+ fieldlist +" FROM "+ name+" WHERE ID="+index+" ;" ; 
		
		try{
			
			stmt =  connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
			// PreparedStatement prep = connection.prepareStatement( sql );
			resultset = stmt.executeQuery(sql);
			 
			// resultset = stmt.execute(sql, Statement.RETURN_GENERATED_KEYS);
			
			z=0; 
			while (resultset.next()){
				z++;
			}
			
			rStr = new String[z];
			resultset.beforeFirst();
			
			while (resultset.next()){
				rowstring ="";
				
				for (int c=1; c<=activeColumnsCount;c++){
					
					obj = resultset.getObject(c);
					
					col = getColumnbyName( fields[c-1]);
					
					cellvalue =  col.getStringfromCellValue( obj );
					
					rowstring = rowstring + cellvalue;
					if (c<activeColumnsCount){
						rowstring = rowstring + " \t" ;
					}
					 
				}
				
			} // resultset ->  getStringfromCellValue( c, obj );
			rStr = new String[1];
			rStr[0] = rowstring;
			
			resultset.close();

			connection.commit();
			
		} catch( SQLException sx ){
			String msg = sx.getMessage();
			
			sx.printStackTrace();
		} 		
				
		
		return rStr;
	}
	
	
	public String[] getColumnNames(int offset, int lastposition){
		return null;
	}
	
	public String[] getColumnNames(){
		
		String[] colnames=null;
		
		colnames = new String[columns.size()];
		
		for (int i=0;i<colnames.length;i++){
			colnames[i] = columns.get(i).labelname ;
		}
		 
		return colnames;
	}
	
	private void getListofColumns(){  
		
		String sql;
		Statement statement;
		ResultSet resultset;
		RowId rowid;
		Array data;
		DocoDbTable table;

		String column_name,type_name ;  
		int ordinal_position,data_type ;
		
		sql = "SELECT  *  FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME ='"+name+"' ; " ;
		
		try{
			PreparedStatement prep = connection.prepareStatement( sql );
			resultset = prep.executeQuery();
			
			while (resultset.next()){
				
				// rowid = resultset.getRowId(1);
				  data = resultset.getArray("COLUMN_NAME");
				
				  column_name = resultset.getString("COLUMN_NAME") ;
				
				if (column_name.length()>0){
					
					dbColumn = new DBColumn();
					
					data_type = resultset.getInt("DATA_TYPE") ; // 4, 12 
					ordinal_position = resultset.getInt("ORDINAL_POSITION");
					type_name = resultset.getString("TYPE_NAME") ; // INTEGER  VARCHAR
					
					dbColumn.datatype = data_type;
					dbColumn.labelname = column_name;
					dbColumn.colposition =ordinal_position;
					dbColumn.typename = type_name;
					 
					columnNames.add(column_name);
					columns.add(dbColumn) ;
					/*
				    TABLE_CATALOG
				    TABLE_SCHEMA
				    TABLE_NAME
				    COLUMN_NAME
				    ORDINAL_POSITION
				    COLUMN_DEFAULT
				    IS_NULLABLE
				    DATA_TYPE
				    CHARACTER_MAXIMUM_LENGTH
				    CHARACTER_OCTET_LENGTH
				    NUMERIC_PRECISION
				    NUMERIC_PRECISION_RADIX
				    NUMERIC_SCALE
				    CHARACTER_SET_NAME
				    COLLATION_NAME
				    TYPE_NAME
				    NULLABLE
				    IS_COMPUTED
				    SELECTIVITY
				    CHECK_CONSTRAINT
				    SEQUENCE_NAME
				    REMARKS
				    SOURCE_DATA_TYPE
				 */
				}
			} // still results ?
			
			resultset.close();
			
			 /*
			statement = connection.createStatement();  
            resultset = statement.executeQuery("SELECT EMPNAME FROM EMPLOYEEDETAILS");  
            while (resultset.next()) {  
                System.out.println(resultset.getString("TABLE_NAME"));  
            } 		
            */
            
			
		}catch(SQLException sx){
			sx.printStackTrace();
		}
	} // end of method:  getListofColumns()
	
	public DBColumn getbyName(String colname){
		 
		String tn;
		DBColumn col=null;
		
		for (int i=0;i<columns.size();i++){
			col = columns.get(i); 
			tn = col.labelname ;
			if (tn.toLowerCase().contentEquals(colname.toLowerCase())){
				break;
			}
		}
		return col;
		 
	}

	
} // end of Table

